-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH INSURANCE1 TABLE
-- IN PARTICULAR SELECT ACCOUNTS AVAILABLE FOR SCORING



\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_batch_insurance1_prescoring_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_efx_count integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id             from batch_insurance1_t limit 1;
SELECT INTO v_efx_count count(*)          from insurance1_batch_efx_in_t; 

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,3,'batch_insurance1_t UPDATE');




-- STEP 1: WE NEED TO RECHECK THE EMDEON FLAGS BECAUSE insurance1_t MAY HAVE BEEN MODIFIED BETWEEN THE TIME insurance0 WAS RUNNING AND NOW;
--


RAISE NOTICE 'START REFRESHING PRIOR FLAGS AND EMDEON FLAGS IN batch_insurance1_t ';


  UPDATE batch_insurance1_t
  SET
     prior_commercial_run     =  (CASE WHEN a.prior_commercial_run = 'Y' THEN 'Y'
                                       ELSE batch_insurance1_t.prior_commercial_run
                                       END),
     prior_tpl_run            =  (CASE WHEN a.prior_tpl_run = 'Y' THEN 'Y'
                                       ELSE batch_insurance1_t.prior_tpl_run
                                       END),
     prior_medicaid_run       =  (CASE WHEN a.prior_medicaid_run = 'Y' THEN 'Y'
                                       ELSE batch_insurance1_t.prior_medicaid_run
                                       END),
     prior_medicare_run       =  (CASE WHEN a.prior_medicare_run = 'Y' THEN 'Y'
                                       ELSE batch_insurance1_t.prior_medicare_run
                                       END),
     emdeon_commercial_flag   =  (CASE WHEN batch_insurance1_t.emdeon_commercial_flag = 'A' THEN 'A'
                                       WHEN a.prior_commercial_run = 'Y'                    THEN 'B'
                                       ELSE batch_insurance1_t.emdeon_commercial_flag
                                       END),
     emdeon_tpl_flag          =  (CASE WHEN batch_insurance1_t.emdeon_tpl_flag = 'A' THEN 'A'
                                       WHEN a.prior_tpl_run = 'Y'                    THEN 'B'
                                       ELSE batch_insurance1_t.emdeon_tpl_flag
                                       END),
     emdeon_medicaid_flag     =  (CASE WHEN batch_insurance1_t.emdeon_medicaid_flag = 'A' THEN 'A'
                                       WHEN a.prior_medicaid_run = 'Y'                    THEN 'B'
                                       ELSE batch_insurance1_t.emdeon_medicaid_flag
                                       END),
     emdeon_medicare_flag     =  (CASE WHEN batch_insurance1_t.emdeon_medicare_flag = 'A' THEN 'A'
                                       WHEN a.prior_medicare_run = 'Y'                    THEN 'B'
                                       ELSE batch_insurance1_t.emdeon_medicare_flag
                                       END),
     acctnum_seq              =  a.acctnum_seq + 1

  FROM

   ( SELECT distinct on (cust_id,hid,acctnum)
            cust_id,
            hid,
            acctnum,
            prior_commercial_run,
            prior_tpl_run,
            prior_medicaid_run,
            prior_medicare_run,
            acctnum_seq
     FROM
           insurance1_t

     ORDER BY
           cust_id,
           hid,
           acctnum,
           acctnum_seq DESC
   ) as a

  WHERE
    (a.cust_id = batch_insurance1_t.cust_id) AND (a.acctnum = batch_insurance1_t.acctnum ) AND (a.hid = batch_insurance1_t.hid);

RAISE NOTICE 'END REFRESHING PRIOR FLAGS AND EMDEON FLAGS IN batch_insurance1_t ';




-- STEP 2: UPDATE RECORDS IN batch_insurance1_t WHICH EQUIFAX SSN;
--

RAISE NOTICE 'START UPDATE RECORDS IN batch_insurance1_t WITH EFX SSN';


IF (v_efx_count > 0) THEN

   UPDATE batch_insurance1_t
   SET
         ssn                 = efx_ssn,
	 processing_ssn_flag = 'P',
	 emdeon_commercial_flag = (CASE WHEN batch_insurance1_t.emdeon_commercial_flag = 'C' and batch_insurance1_t.name_type = 'P' THEN '1'
	                                ELSE batch_insurance1_t.emdeon_commercial_flag
					END),
	 emdeon_tpl_flag        = (CASE WHEN batch_insurance1_t.emdeon_tpl_flag = 'C' and batch_insurance1_t.name_type = 'P' THEN '1'
	                                ELSE batch_insurance1_t.emdeon_tpl_flag
					END),
         emdeon_medicaid_flag   = (CASE WHEN batch_insurance1_t.emdeon_medicaid_flag = 'C' THEN '1'
	                                ELSE batch_insurance1_t.emdeon_medicaid_flag
					END),
	 emdeon_medicare_flag   = (CASE WHEN batch_insurance1_t.emdeon_medicare_flag = 'C' THEN '1'
	                                ELSE batch_insurance1_t.emdeon_medicare_flag
					END)
   FROM
     insurance1_batch_efx_in_t as a
   WHERE
     batch_insurance1_t.cust_id = a.cust_id
	 AND
	 batch_insurance1_t.hid = a.hid
	 AND
	 batch_insurance1_t.acctnum = a.acctnum
	 AND
	 a.efx_ssn IS NOT NULL
	 AND
	 a.name_type = 'P'
   ;



   UPDATE batch_insurance1_t
   SET
     g_ssn                 = efx_ssn,
     processing_ssn_flag   = (CASE WHEN batch_insurance1_t.processing_ssn_flag = 'P' THEN 'B'
	                           ELSE                                               'G'
				   END),
     emdeon_commercial_flag = (CASE WHEN batch_insurance1_t.emdeon_commercial_flag = 'C' and batch_insurance1_t.name_type = 'G' THEN '1'
                                    ELSE batch_insurance1_t.emdeon_commercial_flag
       				    END),
     emdeon_tpl_flag        = (CASE WHEN batch_insurance1_t.emdeon_tpl_flag = 'C' and batch_insurance1_t.name_type = 'G' THEN '1'
  	                            ELSE batch_insurance1_t.emdeon_tpl_flag
				    END)
   FROM
     insurance1_batch_efx_in_t as a
   WHERE
     batch_insurance1_t.cust_id = a.cust_id
	 AND
	 batch_insurance1_t.hid = a.hid
	 AND
	 batch_insurance1_t.acctnum = a.acctnum
	 AND
	 a.efx_ssn IS NOT NULL
	 AND
	 a.name_type = 'G'
   ;


END IF;


RAISE NOTICE 'FINISH UPDATE RECORDS IN batch_insurance1_t WITH EFX SSN';




-- STEP 3: APPLY LOGIC WITH insurance1_prior_t: FIRST DELETION;
--


RAISE NOTICE 'START DELETION OLD RECORDS IN insurance1_prior_t ';

DELETE FROM insurance1_prior_t
WHERE (ml_ddiff_f('mt',insurance1_prior_t.emdeon_sent_date,v_batch_run_date) >= 3 );

RAISE NOTICE 'FINISH DELETION OLD RECORDS IN insurance1_prior_t ';



-- STEP 4 
-- UPDATE insurance1_prior_T WITH loaded_insurance2 = 'Y' FOR ALL PRIOR RUNS WHICH HAVE COMPLETED THE insurance2 MODULE SECTION 


RAISE NOTICE 'START UPDATE insurance1_prior_T WITH loaded_insurance2';


  UPDATE insurance1_prior_t
  SET
    loaded_insurance2 = 'Y'
  FROM
    batch_status_insurance2_t as a
  WHERE
    insurance1_prior_t.loaded_insurance2 = 'N'
    AND
    insurance1_prior_t.cust_id = a.cust_id
    AND
    insurance1_prior_t.emdeon_sent_date = a.last_run_date
    AND
    a.audit_process = 'BATCH DONE'
  ;


RAISE NOTICE 'FINISH UPDATE insurance1_prior_T WITH loaded_insurance2';


 
/*
---------------------------------------------------------------------------------------
-- THIS SECTION NEEDS TO BE COMMENTED OUT IN ORDER TO USE THE REPEATED INDIVIDUAL LOGIC

  UPDATE batch_insurance1_t
  SET
   emdeon_tpl_flag        = (CASE WHEN batch_insurance1_t.emdeon_tpl_flag = '1' AND ba.sum_tpl >= 1               THEN 'I'  
                                  ELSE                                                                            batch_insurance1_t.emdeon_tpl_flag 
                                  END),
   emdeon_commercial_flag = (CASE WHEN batch_insurance1_t.emdeon_commercial_flag = '1' AND ba.sum_commercial >= 1 THEN 'I' 
                                  ELSE                                                                            batch_insurance1_t.emdeon_commercial_flag
                                  END),
   emdeon_medicaid_flag   = (CASE WHEN batch_insurance1_t.emdeon_medicaid_flag = '1' AND ba.sum_medicaid >= 1     THEN 'I' 
                                  ELSE                                                                            batch_insurance1_t.emdeon_medicaid_flag
                                  END),
   emdeon_medicare_flag   = (CASE WHEN batch_insurance1_t.emdeon_medicare_flag = '1' 
                                       AND 
                                       ba.sum_medicare_slow >= 1                                                  THEN 'I' 
                                  WHEN batch_insurance1_t.emdeon_medicare_flag = '1'
                                       AND
                                       ba.sum_medicare_fast >= 1
                                       AND
                                       emdeon_medicare_fast = '1'                                                 THEN 'I'
                                  ELSE                                                                            batch_insurance1_t.emdeon_medicare_flag
                                  END)
  FROM
     (SELECT
        individual_matchkey,
        sum(emdeon_tpl_flag)             as sum_tpl,
        sum(emdeon_commercial_flag)      as sum_commercial,
        sum(emdeon_medicaid_flag)        as sum_medicaid,
        sum(emdeon_medicare_slow_flag)   as sum_medicare_slow,
        sum(emdeon_medicare_fast_flag)   as sum_medicare_fast
      FROM
        insurance1_prior_t 
      WHERE
        loaded_insurance2 = 'Y'
      GROUP BY
        individual_matchkey
     ) as ba
  WHERE
     ba.individual_matchkey = batch_insurance1_t.individual_matchkey
  ;
*/



-- STEP 5 UPDATE batch_status_insurance1_t WITH THE NUMBER TO BE SENT FOR SCORING 
-- UPDATE BATCH STATUS 

  SELECT INTO v_status count(*) from batch_insurance1_t 
  WHERE (emdeon_tpl_flag = '1' OR emdeon_commercial_flag = '1' OR emdeon_medicaid_flag = '1' or emdeon_medicare_flag = '1');

  UPDATE batch_status_insurance1_t
    SET
      batch_insurance1_to_score        = v_status
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,3,'FINISH UPDATE OF batch_insurance1_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
